﻿Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized


Public Class SqlServer2005ScriptGenerator

    Private _serverName As String
    Private _databaseName As String
    Private _userName As String
    Private _password As String
    Private _useWindowsAuthentication As Boolean

    Public Sub New(ByVal serverName As String, _
                   ByVal databaseName As String, _
                   ByVal userName As String, _
                   ByVal password As String, _
                   ByVal useWindowsAuthentication As Boolean)
        Me._serverName = serverName
        Me._databaseName = databaseName
        Me._userName = userName
        Me._password = password
        Me._useWindowsAuthentication = useWindowsAuthentication
    End Sub

    Public Sub GenerateScript()
        'Connect to the local, default instance of SQL Server.
        'Dim connection As New ServerConnection
        'Dim sqlConnection As New System.Data.SqlClient.SqlConnection(connectionString)
        Try
            Dim connection As ServerConnection = Nothing
            Dim srv As Server = Nothing
            If Me._useWindowsAuthentication Then
                If String.IsNullOrEmpty(Me._serverName) Then
                    srv = New Server()
                Else
                    srv = New Server(Me._serverName)
                End If
            Else
                connection = New ServerConnection(Me._serverName, Me._userName, Me._password)
                srv = New Server(connection)
            End If
            'Open the database
            Dim db As Database = srv.Databases(Me._databaseName)
            'Define a Scripter object and set the required scripting options.
            Dim scrp As Scripter
            scrp = New Scripter(srv)
            scrp.Options.ScriptDrops = False
            scrp.Options.WithDependencies = False
            scrp.Options.DriPrimaryKey = True
            scrp.Options.DriForeignKeys = True
            'Iterate through the tables in database and script each one. Display the script.
            'Note that the StringCollection type needs the System.Collections.Specialized namespace to be included.
            Dim tb As Microsoft.SqlServer.Management.Smo.Table
            Dim smoObjects(1) As Urn
            Dim sb As New System.Text.StringBuilder()
            For Each tb In db.Tables
                Dim tableName As String = tb.Name
                RaiseEvent LogEvent("Generating SQL Script: " & tableName)
                Console.WriteLine(tableName)
                smoObjects = New Urn(0) {}
                smoObjects(0) = tb.Urn
                If tb.IsSystemObject = False Then
                    Dim sc As StringCollection
                    sc = scrp.Script(smoObjects)
                    Dim st As String
                    For Each st In sc
                        sb.Append(st).Append(vbCrLf).Append("GO").Append(vbCrLf)
                    Next
                End If
            Next
            RaiseEvent LogEvent("Finished SQL Script Generation")
            RaiseEvent LogEvent("------------------------------")
            RaiseEvent LogEvent(vbCrLf)
            RaiseEvent Finished(sb.ToString())
        Catch ex As ConnectionFailureException
            RaiseEvent ShowError("Error connecting to database: " & ex.Message() & ". Please check your connection settings.", ex.ToString())
            Return
        Catch ex2 As Exception
            RaiseEvent ShowError("Error connecting to database: " & ex2.Message() & ". Please check your connection settings.", ex2.ToString())
            Return
        End Try
    End Sub

    Public Event ShowError(ByVal msg As String, ByVal details As String)

    Public Event LogEvent(ByVal msg As String)

    Public Event Finished(ByVal result As String)

End Class
